package com.ssyt.yt1.day26;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

import com.ssyt.yt1.common.util.DBUtil;

/**
 * <pre>
 * 批处理
 * 1）批处理就是一次性向数据库中执行多条语句，一次提交，提高性能。
 * 2）JDBC的批处理不能加入select语句，否则会抛异常。
 * 3）MySQL批处理配置：
 * a）需要在url中添加参数rewriteBatchedStatements=true
 * b）需要相应JDBC驱动支持（5.1.13、5.1.16、5.1.17）
 * 4）仍然优先选用PreparedStatement
 * 5）有批处理的地方都需要有事务支持！
 * </pre>
 * 
 * @author Hsu.
 * @since 2015年12月30日
 */
public class BatchDemo {

	/**
	 * 传统的批量新增
	 */
	public static void test1() {
		long start = System.currentTimeMillis();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = DBUtil.getConnection();
			connection.setAutoCommit(false);
			String sql = "insert into user values(null, ?, ?, ?, ?, ?, ?)";
			preparedStatement = connection.prepareStatement(sql);
			for (int i = 0; i < 400001; i++) {
				preparedStatement.setString(1, i + "");
				preparedStatement.setString(2, "zhansan");
				preparedStatement.setString(3, "admin");
				preparedStatement.setBigDecimal(4, new BigDecimal("999.99"));
				preparedStatement.setTimestamp(5,
						new Timestamp(System.currentTimeMillis()));
				preparedStatement.setString(6, ".");
				preparedStatement.executeUpdate();
			}
			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(connection, preparedStatement);
		}
		System.out.println("传统的批量新增, 用时："
				+ (System.currentTimeMillis() - start));
	}

	/**
	 * JDBC的批处理操作新增：PreparedStatement（推荐）
	 */
	public static void test2() {
		long start = System.currentTimeMillis();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = DBUtil.getConnection();
			connection.setAutoCommit(false);
			String sql = "insert into user values(null, ?, ?, ?, ?, ?, ?)";
			preparedStatement = connection.prepareStatement(sql);
			for (int i = 0; i < 400001; i++) {
				preparedStatement.setString(1, i + 400002 + "");
				preparedStatement.setString(2, "zhansan");
				preparedStatement.setString(3, "admin");
				preparedStatement.setBigDecimal(4, new BigDecimal("999.99"));
				preparedStatement.setTimestamp(5,
						new Timestamp(System.currentTimeMillis()));
				preparedStatement.setString(6, ".");
				preparedStatement.addBatch();
				// 避免大批量数据添加容易出错的问题！
				if (i % 50 == 0) {
					preparedStatement.executeBatch();
				}
			}
			preparedStatement.executeBatch();
			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(connection, preparedStatement);
		}
		System.out.println("JDBC的批处理操作新增：PreparedStatement（推荐）, 用时："
				+ (System.currentTimeMillis() - start));
	}

	/**
	 * JDBC的批处理操作新增：Statement
	 */
	public static void test3() {
		long start = System.currentTimeMillis();
		Connection connection = null;
		Statement statement = null;
		try {
			connection = DBUtil.getConnection();
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			for (int i = 0; i < 400001; i++) {
				String sql = "insert into user values(null, '" + i + 800004
						+ "', 'zhangsan', 'admin', 999.99, null, null)";
				statement.addBatch(sql);
				// 避免大批量数据添加容易出错的问题！
				if (i % 50 == 0) {
					statement.executeBatch();
				}
			}
			statement.executeBatch();
			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		System.out.println("JDBC的批处理操作新增：Statement, 用时："
				+ (System.currentTimeMillis() - start));
	}

	public static void main(String[] args) {
		test1();
		test2();
		test3();
	}
}
